package com.atguigu.realtime.sugar.mapper;

import com.atguigu.realtime.sugar.bean.Spu;
import com.atguigu.realtime.sugar.bean.Tm;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

public interface ProductMapper {
    
    @Select("SELECT sum(order_amount) AS order_amount\n" +
        "FROM product_stats_2021\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n")
    BigDecimal gmv(@Param("date") int date);
    
    
    /*
    ┌─tm_name───┬─order_amount─┐
    │ 小米      │     82686.00 │
    │ 苹果      │    162743.00 │
    │ Redmi     │     13089.00 │
    │ TCL       │    125288.00 │
    │ 华为      │     41142.00 │
    │ 香奈儿    │      1688.00 │
    │ 索芙特    │      2451.00 │
    │ 长粒香    │       156.00 │
    │ CAREMiLLE │       414.00 │
    │ 金沙河    │        22.00 │
    └───────────┴──────────────┘

     */
    
    @Select("SELECT\n" +
        "    tm_name,\n" +
        "    sum(order_amount) AS order_amount\n" +
        "FROM product_stats_2021\n" +
        "WHERE toYYYYMMDD(stt) =  #{date}\n" +
        "GROUP BY tm_name\n")
    List<Map<String, Object>> gmvByTm(@Param("date") int date);  // 20220329
    
    
    /*
    ┌─category3_name─┬─order_amount─┐
    │ 唇部           │      2865.00 │
    │ 香水           │      1688.00 │
    │ 手机           │    264784.00 │
    │ 米面杂粮       │       178.00 │
    │ 平板电视       │    160164.00 │

     */
    @Select("SELECT\n" +
        "    category3_name,\n" +
        "    sum(order_amount) AS order_amount\n" +
        "FROM product_stats_2021\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY category3_name order by order_amount desc\n")
    List<Tm> gmvByC3(@Param("date") int date);  // 20220329
    
    
    @Select("SELECT\n" +
        "    spu_name,\n" +
        "    sum(order_amount) AS order_amount\n" +
        "FROM product_stats_2021\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY spu_name order by order_amount desc\n")
    List<Spu> gmvBySpu(@Param("date") int date);  // 20220329
    
    
    
}
